Imports System.Data
Imports System.Data.SqlClient

Public Class CreatePackage

    Public Action As String
    Public ID As Integer

    Dim _PackageName As String
    Dim _RefPrice As Decimal
    Dim _Item01 As String
    Dim _Item02 As String
    Dim _Item03 As String
    Dim _Item04 As String
    Dim _Item05 As String
    Dim _Item06 As String
    Dim _Item07 As String
    Dim _Item08 As String
    Dim _Item09 As String
    Dim _Item10 As String
    Dim _Item11 As String
    Dim _Item12 As String
    Dim _Item13 As String
    Dim _Item14 As String
    Dim _Item15 As String
    Dim _Item16 As String
    Dim _Item17 As String
    Dim _Item18 As String
    Dim _Item19 As String
    Dim _Item20 As String
    Dim _Item01YesNo As Integer
    Dim _Item02YesNo As Integer
    Dim _Item03YesNo As Integer
    Dim _Item04YesNo As Integer
    Dim _Item05YesNo As Integer
    Dim _Item06YesNo As Integer
    Dim _Item07YesNo As Integer
    Dim _Item08YesNo As Integer
    Dim _Item09YesNo As Integer
    Dim _Item10YesNo As Integer
    Dim _Item11YesNo As Integer
    Dim _Item12YesNo As Integer
    Dim _Item13YesNo As Integer
    Dim _Item14YesNo As Integer
    Dim _Item15YesNo As Integer
    Dim _Item16YesNo As Integer
    Dim _Item17YesNo As Integer
    Dim _Item18YesNo As Integer
    Dim _Item19YesNo As Integer
    Dim _Item20YesNo As Integer



    Private Sub CreatePackage_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadIcon(Me)
        If Action = "EDIT" Then
            Dim str As String
            str = "SELECT * FROM Packages WHERE ID = " & ID
            Dim cmd As New SqlCommand(str, gCnnPCE)
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader
            While dr.Read
                Me.txtPackageName.Text = dr.Item("PackageName").ToString
                Me.txtRefPrice.Text = dr.Item("RefPrice").ToString
                Me.txtItem01.Text = dr.Item("Item01").ToString
                Me.txtItem02.Text = dr.Item("Item02").ToString
                Me.txtItem03.Text = dr.Item("Item03").ToString
                Me.txtItem04.Text = dr.Item("Item04").ToString
                Me.txtItem05.Text = dr.Item("Item05").ToString
                Me.txtItem06.Text = dr.Item("Item06").ToString
                Me.txtItem07.Text = dr.Item("Item07").ToString
                Me.txtItem08.Text = dr.Item("Item08").ToString
                Me.txtItem09.Text = dr.Item("Item09").ToString
                Me.txtItem10.Text = dr.Item("Item10").ToString
                Me.txtItem11.Text = dr.Item("Item11").ToString
                Me.txtItem12.Text = dr.Item("Item12").ToString
                Me.txtItem13.Text = dr.Item("Item13").ToString
                Me.txtItem14.Text = dr.Item("Item14").ToString
                Me.txtItem15.Text = dr.Item("Item15").ToString
                Me.txtItem16.Text = dr.Item("Item16").ToString
                Me.txtItem17.Text = dr.Item("Item17").ToString
                Me.txtItem18.Text = dr.Item("Item18").ToString
                Me.txtItem19.Text = dr.Item("Item19").ToString
                Me.txtItem20.Text = dr.Item("Item20").ToString

            End While
            dr.Close()

        End If
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub



    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If DxValidationProvider1.Validate = False Then
            Exit Sub
        End If

        _PackageName = Me.txtPackageName.Text
        _RefPrice = Me.txtRefPrice.Text
        _Item01 = Me.txtItem01.Text
        _Item02 = Me.txtItem02.Text
        _Item03 = Me.txtItem03.Text
        _Item04 = Me.txtItem04.Text
        _Item05 = Me.txtItem05.Text
        _Item06 = Me.txtItem06.Text
        _Item07 = Me.txtItem07.Text
        _Item08 = Me.txtItem08.Text
        _Item09 = Me.txtItem09.Text
        _Item10 = Me.txtItem10.Text
        _Item11 = Me.txtItem11.Text
        _Item12 = Me.txtItem12.Text
        _Item13 = Me.txtItem13.Text
        _Item14 = Me.txtItem14.Text
        _Item15 = Me.txtItem15.Text
        _Item16 = Me.txtItem16.Text
        _Item17 = Me.txtItem17.Text
        _Item18 = Me.txtItem18.Text
        _Item19 = Me.txtItem19.Text
        _Item20 = Me.txtItem20.Text
        _Item01YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item02YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item03YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item04YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item05YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item06YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item07YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item08YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item09YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item10YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item11YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item12YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item13YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item14YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item15YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item16YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item17YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item18YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item19YesNo = IIf(Me.txtItem01.Text = "", 0, 1)
        _Item20YesNo = IIf(Me.txtItem01.Text = "", 0, 1)

        If Action = "ADD" Then

            INSERT2Packages(_PackageName, _RefPrice, _Item01, _Item02, _Item03, _Item04, _Item05, _Item06, _Item07, _
                                            _Item08, _Item09, _Item10, _Item11, _Item12, _Item13, _Item14, _Item15, _
                                            _Item16, _Item17, _Item18, _Item19, _Item20, _Item01YesNo, _Item02YesNo, _Item03YesNo, _
                                            _Item04YesNo, _Item05YesNo, _Item06YesNo, _Item07YesNo, _Item08YesNo, _
                                            _Item09YesNo, _Item10YesNo, _Item11YesNo, _Item12YesNo, _Item13YesNo, _
                                            _Item14YesNo, _Item15YesNo, _Item16YesNo, _Item17YesNo, _Item18YesNo, _Item19YesNo, _
                                            _Item20YesNo)

        Else
            UPDATE2Packages(_PackageName, _RefPrice, _Item01, _Item02, _Item03, _Item04, _Item05, _Item06, _Item07, _
                                         _Item08, _Item09, _Item10, _Item11, _Item12, _Item13, _Item14, _Item15, _
                                         _Item16, _Item17, _Item18, _Item19, _Item20, _Item01YesNo, _Item02YesNo, _Item03YesNo, _
                                         _Item04YesNo, _Item05YesNo, _Item06YesNo, _Item07YesNo, _Item08YesNo, _
                                         _Item09YesNo, _Item10YesNo, _Item11YesNo, _Item12YesNo, _Item13YesNo, _
                                         _Item14YesNo, _Item15YesNo, _Item16YesNo, _Item17YesNo, _Item18YesNo, _Item19YesNo, _
                                         _Item20YesNo)
        End If


    End Sub



    Private Sub INSERT2Packages( _
        ByVal PackageName As String, ByVal RefPrice As Decimal, ByVal Item01 As String, ByVal Item02 As String, ByVal Item03 As String, _
        ByVal Item04 As String, ByVal Item05 As String, ByVal Item06 As String, ByVal Item07 As String, ByVal Item08 As String, _
        ByVal Item09 As String, ByVal Item10 As String, ByVal Item11 As String, ByVal Item12 As String, ByVal Item13 As String, _
        ByVal Item14 As String, ByVal Item15 As String, ByVal Item16 As String, ByVal Item17 As String, ByVal Item18 As String, _
        ByVal Item19 As String, ByVal Item20 As String, ByVal Item01YesNo As Integer, ByVal Item02YesNo As Integer, ByVal Item03YesNo As Integer, _
        ByVal Item04YesNo As Integer, ByVal Item05YesNo As Integer, ByVal Item06YesNo As Integer, ByVal Item07YesNo As Integer, ByVal Item08YesNo As Integer, _
        ByVal Item09YesNo As Integer, ByVal Item10YesNo As Integer, ByVal Item11YesNo As Integer, ByVal Item12YesNo As Integer, ByVal Item13YesNo As Integer, _
        ByVal Item14YesNo As Integer, ByVal Item15YesNo As Integer, ByVal Item16YesNo As Integer, ByVal Item17YesNo As Integer, ByVal Item18YesNo As Integer, _
        ByVal Item19YesNo As Integer, ByVal Item20YesNo As Integer)

        Dim str As String
        Dim cmd As New SqlCommand(str, gCnnPCE)
        str = "INSERT INTO Packages(" & _
        "PackageName, RefPrice, Item01, Item02, Item03, Item04, Item05, Item06, Item07, Item08," & _
        "Item09, Item10, Item11, Item12, Item13, Item14, Item15, Item16, Item17, Item18, Item19, Item20, Item01YesNo, Item02YesNo, Item03YesNo," & _
        "Item04YesNo, Item05YesNo, Item06YesNo, Item07YesNo, Item08YesNo," & _
        "Item09YesNo, Item10YesNo, Item11YesNo, Item12YesNo, Item13YesNo, Item14YesNo, Item15YesNo, Item16YesNo, Item17YesNo, Item18YesNo," & _
        "Item19YesNo, Item20YesNo, CreatedDateTime) VALUES ( " & _
        "@PackageName, @RefPrice, @Item01, @Item02, @Item03, @Item04, @Item05, @Item06, @Item07, @Item08," & _
        "@Item09, @Item10, @Item11, @Item12, @Item13, @Item14, @Item15, @Item16, @Item17, @Item18, @Item19, @Item20, @Item01YesNo, @Item02YesNo, @Item03YesNo," & _
        "@Item04YesNo, @Item05YesNo, @Item06YesNo, @Item07YesNo, @Item08YesNo," & _
        "@Item09YesNo, @Item10YesNo, @Item11YesNo, @Item12YesNo, @Item13YesNo, @Item14YesNo, @Item15YesNo, @Item16YesNo, @Item17YesNo, @Item18YesNo," & _
        "@Item19YesNo, @Item20YesNo, @CreatedDateTime) "

        cmd.CommandText = str
        cmd.Parameters.AddWithValue("@PackageName", PackageName)
        cmd.Parameters.AddWithValue("@RefPrice", RefPrice)
        cmd.Parameters.AddWithValue("@Item01", Item01)
        cmd.Parameters.AddWithValue("@Item02", Item02)
        cmd.Parameters.AddWithValue("@Item03", Item03)
        cmd.Parameters.AddWithValue("@Item04", Item04)
        cmd.Parameters.AddWithValue("@Item05", Item05)
        cmd.Parameters.AddWithValue("@Item06", Item06)
        cmd.Parameters.AddWithValue("@Item07", Item07)
        cmd.Parameters.AddWithValue("@Item08", Item08)
        cmd.Parameters.AddWithValue("@Item09", Item09)
        cmd.Parameters.AddWithValue("@Item10", Item10)
        cmd.Parameters.AddWithValue("@Item11", Item11)
        cmd.Parameters.AddWithValue("@Item12", Item12)
        cmd.Parameters.AddWithValue("@Item13", Item13)
        cmd.Parameters.AddWithValue("@Item14", Item14)
        cmd.Parameters.AddWithValue("@Item15", Item15)
        cmd.Parameters.AddWithValue("@Item16", Item16)
        cmd.Parameters.AddWithValue("@Item17", Item17)
        cmd.Parameters.AddWithValue("@Item18", Item18)
        cmd.Parameters.AddWithValue("@Item19", Item19)
        cmd.Parameters.AddWithValue("@Item20", Item20)
        cmd.Parameters.AddWithValue("@Item01YesNo", Item01YesNo)
        cmd.Parameters.AddWithValue("@Item02YesNo", Item02YesNo)
        cmd.Parameters.AddWithValue("@Item03YesNo", Item03YesNo)
        cmd.Parameters.AddWithValue("@Item04YesNo", Item04YesNo)
        cmd.Parameters.AddWithValue("@Item05YesNo", Item05YesNo)
        cmd.Parameters.AddWithValue("@Item06YesNo", Item06YesNo)
        cmd.Parameters.AddWithValue("@Item07YesNo", Item07YesNo)
        cmd.Parameters.AddWithValue("@Item08YesNo", Item08YesNo)
        cmd.Parameters.AddWithValue("@Item09YesNo", Item09YesNo)
        cmd.Parameters.AddWithValue("@Item10YesNo", Item10YesNo)
        cmd.Parameters.AddWithValue("@Item11YesNo", Item11YesNo)
        cmd.Parameters.AddWithValue("@Item12YesNo", Item12YesNo)
        cmd.Parameters.AddWithValue("@Item13YesNo", Item13YesNo)
        cmd.Parameters.AddWithValue("@Item14YesNo", Item14YesNo)
        cmd.Parameters.AddWithValue("@Item15YesNo", Item15YesNo)
        cmd.Parameters.AddWithValue("@Item16YesNo", Item16YesNo)
        cmd.Parameters.AddWithValue("@Item17YesNo", Item17YesNo)
        cmd.Parameters.AddWithValue("@Item18YesNo", Item18YesNo)
        cmd.Parameters.AddWithValue("@Item19YesNo", Item19YesNo)
        cmd.Parameters.AddWithValue("@Item20YesNo", Item20YesNo)
        cmd.Parameters.AddWithValue("@CreatedDateTime", Now)

        If cmd.ExecuteNonQuery() > 0 Then
            MsgBox("Save Successfully", MsgBoxStyle.Information)
            Me.Close()
        Else
            MsgBox("Fail to Save", MsgBoxStyle.Exclamation)
        End If


    End Sub

    Private Sub UPDATE2Packages( _
    ByVal PackageName As String, ByVal RefPrice As Decimal, ByVal Item01 As String, ByVal Item02 As String, ByVal Item03 As String, _
    ByVal Item04 As String, ByVal Item05 As String, ByVal Item06 As String, ByVal Item07 As String, ByVal Item08 As String, _
    ByVal Item09 As String, ByVal Item10 As String, ByVal Item11 As String, ByVal Item12 As String, ByVal Item13 As String, _
    ByVal Item14 As String, ByVal Item15 As String, ByVal Item16 As String, ByVal Item17 As String, ByVal Item18 As String, _
    ByVal Item19 As String, ByVal Item20 As String, ByVal Item01YesNo As Integer, ByVal Item02YesNo As Integer, ByVal Item03YesNo As Integer, _
    ByVal Item04YesNo As Integer, ByVal Item05YesNo As Integer, ByVal Item06YesNo As Integer, ByVal Item07YesNo As Integer, ByVal Item08YesNo As Integer, _
    ByVal Item09YesNo As Integer, ByVal Item10YesNo As Integer, ByVal Item11YesNo As Integer, ByVal Item12YesNo As Integer, ByVal Item13YesNo As Integer, _
    ByVal Item14YesNo As Integer, ByVal Item15YesNo As Integer, ByVal Item16YesNo As Integer, ByVal Item17YesNo As Integer, ByVal Item18YesNo As Integer, _
    ByVal Item19YesNo As Integer, ByVal Item20YesNo As Integer)

        Dim str As String
        Dim cmd As New SqlCommand(str, gCnnPCE)
        str = "UPDATE Packages SET " & _
      "PackageName= @PackageName, RefPrice= @RefPrice, Item01= @Item01, Item02= @Item02," & _
      "Item03= @Item03, Item04= @Item04, Item05= @Item05, Item06= @Item06, Item07= @Item07," & _
      "Item08= @Item08, Item09= @Item09, Item10= @Item10, Item11= @Item11, Item12= @Item12," & _
      "Item13= @Item13, Item14= @Item14, Item15= @Item15, Item16= @Item16, Item17= @Item17," & _
      "Item18= @Item18, Item19= @Item19, Item20= @Item20, Item01YesNo= @Item01YesNo, Item02YesNo= @Item02YesNo," & _
      "Item03YesNo= @Item03YesNo, Item04YesNo= @Item04YesNo, Item05YesNo= @Item05YesNo, Item06YesNo= @Item06YesNo, Item07YesNo= @Item07YesNo," & _
      "Item08YesNo= @Item08YesNo, Item09YesNo= @Item09YesNo, Item10YesNo= @Item10YesNo, Item11YesNo= @Item11YesNo, Item12YesNo= @Item12YesNo," & _
      "Item13YesNo= @Item13YesNo, Item14YesNo= @Item14YesNo, Item15YesNo= @Item15YesNo, Item16YesNo= @Item16YesNo, Item17YesNo= @Item17YesNo," & _
      "Item18YesNo= @Item18YesNo, Item19YesNo= @Item19YesNo, Item20YesNo= @Item20YesNo " & _
      " WHERE ID = " & ID

        cmd.CommandText = str
        cmd.Parameters.AddWithValue("@PackageName", PackageName)
        cmd.Parameters.AddWithValue("@RefPrice", RefPrice)
        cmd.Parameters.AddWithValue("@Item01", Item01)
        cmd.Parameters.AddWithValue("@Item02", Item02)
        cmd.Parameters.AddWithValue("@Item03", Item03)
        cmd.Parameters.AddWithValue("@Item04", Item04)
        cmd.Parameters.AddWithValue("@Item05", Item05)
        cmd.Parameters.AddWithValue("@Item06", Item06)
        cmd.Parameters.AddWithValue("@Item07", Item07)
        cmd.Parameters.AddWithValue("@Item08", Item08)
        cmd.Parameters.AddWithValue("@Item09", Item09)
        cmd.Parameters.AddWithValue("@Item10", Item10)
        cmd.Parameters.AddWithValue("@Item11", Item11)
        cmd.Parameters.AddWithValue("@Item12", Item12)
        cmd.Parameters.AddWithValue("@Item13", Item13)
        cmd.Parameters.AddWithValue("@Item14", Item14)
        cmd.Parameters.AddWithValue("@Item15", Item15)
        cmd.Parameters.AddWithValue("@Item16", Item16)
        cmd.Parameters.AddWithValue("@Item17", Item17)
        cmd.Parameters.AddWithValue("@Item18", Item18)
        cmd.Parameters.AddWithValue("@Item19", Item19)
        cmd.Parameters.AddWithValue("@Item20", Item20)
        cmd.Parameters.AddWithValue("@Item01YesNo", Item01YesNo)
        cmd.Parameters.AddWithValue("@Item02YesNo", Item02YesNo)
        cmd.Parameters.AddWithValue("@Item03YesNo", Item03YesNo)
        cmd.Parameters.AddWithValue("@Item04YesNo", Item04YesNo)
        cmd.Parameters.AddWithValue("@Item05YesNo", Item05YesNo)
        cmd.Parameters.AddWithValue("@Item06YesNo", Item06YesNo)
        cmd.Parameters.AddWithValue("@Item07YesNo", Item07YesNo)
        cmd.Parameters.AddWithValue("@Item08YesNo", Item08YesNo)
        cmd.Parameters.AddWithValue("@Item09YesNo", Item09YesNo)
        cmd.Parameters.AddWithValue("@Item10YesNo", Item10YesNo)
        cmd.Parameters.AddWithValue("@Item11YesNo", Item11YesNo)
        cmd.Parameters.AddWithValue("@Item12YesNo", Item12YesNo)
        cmd.Parameters.AddWithValue("@Item13YesNo", Item13YesNo)
        cmd.Parameters.AddWithValue("@Item14YesNo", Item14YesNo)
        cmd.Parameters.AddWithValue("@Item15YesNo", Item15YesNo)
        cmd.Parameters.AddWithValue("@Item16YesNo", Item16YesNo)
        cmd.Parameters.AddWithValue("@Item17YesNo", Item17YesNo)
        cmd.Parameters.AddWithValue("@Item18YesNo", Item18YesNo)
        cmd.Parameters.AddWithValue("@Item19YesNo", Item19YesNo)
        cmd.Parameters.AddWithValue("@Item20YesNo", Item20YesNo)
        cmd.Parameters.AddWithValue("@CreatedDateTime", Now)

        If cmd.ExecuteNonQuery() > 0 Then
            MsgBox("Save Successfully", MsgBoxStyle.Information)
            Me.Close()
        Else
            MsgBox("Fail to Save", MsgBoxStyle.Exclamation)
        End If


    End Sub
End Class